<?php

class ProductModel extends Model
{
    public function getProductsByParam($fieldOrder, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product
            ORDER BY '.$fieldOrder.' DESC
        ', $limitOptions['limit'], $limitOptions['offset']),
            'product_id');
    }

    public function getProductById($productId)
    {
        return $this->_db->fetchRow('
            SELECT *
            FROM product
            WHERE product_id = ?
           ', $productId);
    }

    public function getProductAttribute($productId, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT distinct b.*
            FROM product_to_attribute a, product_attribute b
            WHERE a.attribute_id = b.parentId AND a.product_id = ?
           ', $limitOptions['limit'], $limitOptions['offset']), $productId);
    }


    public function getGroupProduct($parentId, $fieldOrder, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product p, product_category c
            WHERE p.product_id = c.product_category_id AND c.parent_id = ?
            ORDER BY '. $fieldOrder . ' DESC
           ', $limitOptions['limit'], $limitOptions['offset']), $parentId);
    }

    public function getProducts(array $conditions = array(), array $fetchOptions = array())
    {
        $whereClause = $this->prepareProductConditions($conditions);
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAllKeyed($this->_db->limitQueryResults('
            SELECT *
            FROM product
            WHERE '. $whereClause .'
        ', $limitOptions['limit'], $limitOptions['offset']),
            'product_id'
        );
    }

    public function prepareProductConditions(array $conditions)
    {
        $sqlConditions = array();

        if (!empty($conditions['product_name']))
        {
            $sqlConditions[] = 'name LIKE ' . $this->_db->quoteLike('%' . $conditions['product_name'] . '%');
        }

        if (!empty($conditions['from_price']) && !empty($conditions['to_price']))
        {
            $sqlConditions[] = 'price BETWEEN ' . $conditions['from_price'] . ' AND ' . $conditions['to_price'];
        }
        elseif (!empty($conditions['from_price']))
        {
            $sqlConditions[] = 'price > ' . $conditions['from_price'];
        }
        elseif (!empty($conditions['to_price']))
        {
            $sqlConditions[] = 'price < ' . $conditions['to_price'];
        }

        return $this->_db->getConditionsForClause($sqlConditions);
    }

    public function getProductByCategory($productCategoryId, $fieldOrder, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product
            WHERE category_id = ?
            ORDER BY '.$fieldOrder.' DESC
            ', $limitOptions['limit'], $limitOptions['offset']),
            $productCategoryId);
    }

    public function getFeaturedProduct($fieldOrder, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product
            WHERE is_top = 1
            ORDER BY '.$fieldOrder.' DESC
            ', $limitOptions['limit'], $limitOptions['offset']),
            'product_id');
    }

    public function getDiscountingProduct(array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product
            WHERE promotion_price > 0
            ORDER BY promotion_price DESC
            ', $limitOptions['limit'], $limitOptions['offset']),
            'product_id');
    }

    public function getProductComment($productId, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT a.content as comment_content, a.date_created as comment_date, b.name as customer_name, b.avatar as customer_avatar
            FROM product_comment a, customer b
            WHERE a.customer_id = b.customer_id AND product_id = ?
            ORDER BY a.date_created DESC
            ', $limitOptions['limit'], $limitOptions['offset']),
            $productId);
    }

    public function getCustomerComment($productId, $fieldOrder, array $fetchOptions = array())
    {
        $limitOptions = $this->_db->prepareLimitFetchOptions($fetchOptions);
        return $this->_db->fetchAll($this->_db->limitQueryResults('
            SELECT *
            FROM product_comment
            WHERE product_id = ?
            ORDER BY '.$fieldOrder.' DESC
            ', $limitOptions['limit'], $limitOptions['offset']),
            $productId);
    }

    public function getAddComment($productId, $customerId, $content, $date)
    {
        $tableName = 'product_comment';
        $data = array(
            'customer_id' => $customerId,
            'product_id' => $productId,
            'content' => $content,
            'date_created' => $date
        );
        $this->_insert($tableName, $data);
    }
}
